Alter PROC SP_CREATE_UPDATE_CLASS_STORE_LOT (
 
 @PART_NUMBER		VARCHAR(50), 
 @LOT_NUMBER		VARCHAR(50), 
 @QTY_IN			INT,
 @QTY_OUT			INT,
 @DATE_IN			DATETIME,
 @DATE_OUT			DATETIME,
 @INVENTORY_TYPE	VARCHAR(50),
 @PS_TYPE_NAME		VARCHAR(50),
 @VENDOR_NAME		VARCHAR(50),
 @VENDOR_LOTNUMBER	VARCHAR(50),
 @STATUS			VARCHAR(50),
 @WAFER_ID			VARCHAR(100),
 @comments			text,
 @lot_category		varchar(10),
 @fab_lot_Number	VARCHAR(50),
 @stage_name		VARCHAR(50),
 @date_code 		VARCHAR(50),
 @PO_NUMBER			VARCHAR(50)
 )
 
 AS
 BEGIN
 
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @THIS_LOT_STATUS VARCHAR(50)
 	DECLARE @LOT_DATEIN DATETIME

	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end 
	
	if @fab_lot_number is null
	begin
		set @fab_lot_number = @LOT_NUMBER
	end 
	
 	SELECT @PROCESS_STEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE PS_TYPE_NAME = @PS_TYPE_NAME AND PS_TYPE = @INVENTORY_TYPE AND P.PART_NUMBER = @PART_NUMBER AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
 	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
 	BEGIN
 		SELECT @PROCESS_STEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE PMT,PROCESS_STEP PS WHERE PMT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = @PS_TYPE_NAME AND PS_TYPE = @INVENTORY_TYPE AND PMT.PROCESSSTEP_ID=PS.PROCESSSTEP_ID
 		IF @PROCESS_STEP_ID IS NULL
 		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = ISNULL(@fab_lot_Number,@lot_number) AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @inventory_type AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty, Wafer_id, Status, TimeStamp )
					VALUES ( @part_number, @lot_number, ISNULL(@fab_lot_Number,@lot_number) , @vendor_name, @inventory_type,'Discrepancy Lot Notification', @Qty_In, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
			END
			 			
 			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
 			RETURN
 		END
		else
		begin
			UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
		end
 	END
 
 	SELECT @THIS_LOT_STATUS = LOT_CURRENT_STATUS, @LOT_ID = LOT_ID, @LOT_DATEIN = DATE_IN FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER

	IF @DATE_IN IS NULL AND @LOT_DATEIN IS NULL
    BEGIN
        SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
    END
    ELSE
    BEGIN
         IF @DATE_IN IS NULL AND @LOT_DATEIN IS NOT NULL
		 BEGIN	
			SET @DATE_IN = @LOT_DATEIN
		 END
    END

 	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT, STAGE_NAME, LOT_DATE_CODE, PO_NUMBER) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, ISNULL(@fab_lot_Number,@lot_number), ISNULL(@VENDOR_LOTNUMBER,@LOT_NUMBER) , @QTY_IN, ISNULL(@DATE_IN,CURRENT_TIMESTAMP) ,@DATE_OUT, @STATUS, CURRENT_TIMESTAMP,1, @lot_Category, 0, @STAGE_name, @date_code, @PO_NUMBER )
		SELECT @LOT_ID = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name,  Die_Qty, Wafer_id, Status, TimeStamp )
				VALUES ( @part_number, @lot_number, ISNULL(@fab_lot_Number,@lot_number), @vendor_name, @inventory_type, 'New Lot Notification', @qty_in, @wafer_id, 'Open', CURRENT_TIMESTAMP )
		
		INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, TIMESTAMP) VALUES (@LOT_ID, @QTY_IN, @DATE_IN, CURRENT_TIMESTAMP )		
		
	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS,  QTY_IN = @QTY_IN, LOT_CATEGORY = @lot_Category, LOT_DROP_OUT = 0, VENDOR_LOT_NUMBER = ISNULL(@VENDOR_LOTNUMBER,@LOT_NUMBER), STAGE_NAME = @stage_name, LOT_DATE_CODE= @date_code, PO_NUMBER = @PO_NUMBER WHERE LOT_ID = @LOT_ID
	
		UPDATE INVENTORY_LOT SET  IL_QTY_IN = @QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
	END

	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @fab_lot_Number, @QTY_IN, @DATE_IN
 
 END

 